package com.xuxueli.applyModules.utils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Strings;
import com.xuxueli.applyModules.entity.DcBillHeadTmpParam;
import com.xuxueli.applyModules.entity.DcBillListTmpParam;
import com.xuxueli.applyModules.entity.DcBillTmpParam;
import com.xuxueli.applyModules.entity.KeyValuePair;
import com.xuxueli.applyModules.exception.ErrorException;
import org.apache.poi.ss.usermodel.*;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel 读取数据
 */
public class ExcelImportUtil {

    /***
     * 字段属性的名称所在的行
     */
    private static final short FIELD_NAME_ROW_NUM = 1;

    /***
     *
     * @param stream
     * @param sheetName
     * @param startRow
     * @return
     */
    public static List<List<String>> read(InputStream stream, String sheetName, int startRow) throws IOException {
        List<List<String>> result = new ArrayList<>();
        Workbook workbook = null;
        workbook = WorkbookFactory.create(stream);
        Sheet sheet = getSheet(workbook, sheetName);
        DataFormatter objDefaultFormat = new DataFormatter();
        FormulaEvaluator objFormulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        for (Row row : sheet) {
            if (row.getRowNum() < startRow) {
                continue;
            }

            List<String> rowData = new ArrayList<>(row.getLastCellNum());
            result.add(rowData);
            for (Cell cell : row) {
                objFormulaEvaluator.evaluate(cell);
                String cellValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                rowData.add(cellValue);
            }
        }
        return result;
    }

    /***
     *
     * @param stream
     * @param cls
     * @param sheetName
     * @param startRow
     * @param <E>
     * @return
     * @throws IOException
     */
    public static <E extends Object> List<E> read(InputStream stream, Class<E> cls, String sheetName, int startRow) throws IOException, NoSuchFieldException {
        List<E> list = new ArrayList<>();
        Workbook workbook = WorkbookFactory.create(stream);
        Sheet sheet = getSheet(workbook, sheetName);
        List<String> fieldList = new ArrayList<>();
        DataFormatter objDefaultFormat = new DataFormatter();
        FormulaEvaluator objFormulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        for (Row row : sheet) {
            if (row.getRowNum() == FIELD_NAME_ROW_NUM) {
                for (Cell cell : row) {
                    objFormulaEvaluator.evaluate(cell);
                    String cellValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                    if (!Strings.isNullOrEmpty(cellValue)) {
                        fieldList.add(cellValue.trim());
                    }
                }
            }

            if (row.getRowNum() < startRow -1) {
                continue;
            }

            if (fieldList.size() == 0) {
                throw new ErrorException(400, "选择的模板下载错误，导入sheet名称为："+sheetName);
            }

            Map<String, Object> map = new HashMap<>(fieldList.size());
            boolean emptyRow = true;
            for (Cell cell : row) {
                if (fieldList.size() > cell.getColumnIndex()) {
                    objFormulaEvaluator.evaluate(cell);
                    String cellValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);

                    if (emptyRow && !Strings.isNullOrEmpty(cellValue)) {
                        emptyRow = false;
                    }
                    String key = fieldList.get(cell.getColumnIndex());
                    Field field = cls.getDeclaredField(key);

                    Object value = converToObject(cellValue, field.getType().getSimpleName());
                    map.put(key, value);
                }
            }
            if (emptyRow) {
                return list;
            }

            list.add(JSONObject.toJavaObject((JSON) map, cls));

        }
        return list;
    }

    public static DcBillTmpParam readIntegration(InputStream stream, String sheetName, int startRow, List<KeyValuePair<String, String>> headList,
                                                 List<KeyValuePair<String, String>> listList) throws IOException, NoSuchFieldException {
        DcBillTmpParam dcBillTmpParam = new DcBillTmpParam();
        Workbook workbook = WorkbookFactory.create(stream);
        Sheet sheet = getSheet(workbook, sheetName);
        if(sheet==null) {
            throw new ErrorException(400, "选择的导入模板下载错误，导入sheet名称为：" + sheetName);
        }

        int headListSize = headList.size();
        int listListSize = listList.size();
        Map<String, Object> headMap = new HashMap<>(headListSize);
        Map<String, Object> listMap = new HashMap<>(listListSize);

        List<DcBillHeadTmpParam> dcBillHeadTmpParams = new ArrayList<>();
        List<DcBillListTmpParam> dcBillListTmpParams = new ArrayList<>();
        DataFormatter objDefaultFormat = new DataFormatter();
        FormulaEvaluator objFormulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        for (Row row : sheet) {
            if (row.getRowNum() < startRow -1) {
                continue;
            }
            headMap.clear();
            listMap.clear();
            boolean emptyRow = true;
            int rowSize = row.getLastCellNum();
            Cell firstCell = row.getCell(0);
            objFormulaEvaluator.evaluate(firstCell);
            String firstCellValue = objDefaultFormat.formatCellValue(firstCell, objFormulaEvaluator);
            if(!Strings.isNullOrEmpty(firstCellValue)){
                if("HEAD".equals(firstCellValue.toUpperCase())){
                    for (Cell cell : row) {
                        if(cell.getColumnIndex()==0){
                            continue;
                        }
                        if (headListSize >= cell.getColumnIndex() && rowSize > cell.getColumnIndex()) {
                            objFormulaEvaluator.evaluate(cell);
                            String cellValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);

                            if (emptyRow && !Strings.isNullOrEmpty(cellValue.trim())) {
                                emptyRow = false;
                            }

                            String key = headList.get(cell.getColumnIndex()-1).getKey();
                            if(!Strings.isNullOrEmpty(key)) {
                                Field field = DcBillHeadTmpParam.class.getDeclaredField(key);

                                Object value = converToObject(cellValue.trim(), field.getType().getSimpleName());
                                headMap.put(key, value);
                            }
                        }
                    }
                    try {
                        //先转校验实体，校验完后置错误标识再转需要的实体返回
                        //todo
                        dcBillHeadTmpParams.add(JSONObject.toJavaObject((JSON) headMap,DcBillHeadTmpParam.class));
                    } catch (Exception e) {
                        throw new ErrorException(400, "导入模板中导入行数或者导入数据类型错误！");
                    }
                }
                if("LIST".equals(firstCellValue.toUpperCase())){
                    for (Cell cell : row) {
                        if(cell.getColumnIndex()==0){
                            continue;
                        }
                        if (listListSize >= cell.getColumnIndex() && rowSize > cell.getColumnIndex()) {
                            objFormulaEvaluator.evaluate(cell);
                            String cellValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);

                            if (emptyRow && !Strings.isNullOrEmpty(cellValue.trim())) {
                                emptyRow = false;
                            }

                            String key = listList.get(cell.getColumnIndex()-1).getKey();
                            if(!Strings.isNullOrEmpty(key)) {
                                Field field = DcBillListTmpParam.class.getDeclaredField(key);

                                Object value = converToObject(cellValue.trim(), field.getType().getSimpleName());
                                listMap.put(key, value);
                            }
                        }
                    }
                    try {
                        //先转校验实体，校验完后置错误标识再转需要的实体返回
                        //todo
                        dcBillListTmpParams.add(JSONObject.toJavaObject((JSON) listMap,DcBillListTmpParam.class));
                    } catch (Exception e) {
                        throw new ErrorException(400, "导入模板中导入行数或者导入数据类型错误！");
                    }
                }
                if (emptyRow) {
                    break;
                }
            }
        }
        if(dcBillHeadTmpParams.size()!=1){
            throw new ErrorException(400, "模板中HEAD数据只能有一条");
        }
        if (dcBillListTmpParams.size()<=0) {
            throw new ErrorException(400, "模板中LIST数据必须存在");
        }
        dcBillTmpParam.setBillHead(dcBillHeadTmpParams.get(0));
        dcBillTmpParam.setBillLists(dcBillListTmpParams);
        return dcBillTmpParam;
    }

    /***
     *
     * @param cellValue
     * @param typeName
     * @return
     */
    private static Object converToObject(String cellValue, String typeName) {
        switch (typeName) {
            case "Date":
                return DateUtils.stringToDate(cellValue, "yyyy-MM-dd");
            default:
                return cellValue;
        }
    }

    /**
     * @param workbook
     * @param sheetName
     * @return
     */
    private static Sheet getSheet(Workbook workbook, String sheetName) {
        Sheet sheet;
        if (Strings.isNullOrEmpty(sheetName)) {
            sheet = workbook.getSheetAt(0);
        } else {
            sheet = workbook.getSheet(sheetName);
        }

        if (sheet == null) {
            throw new ErrorException(400, "选择的模板下载错误，导入sheet名称为："+sheetName);
        }
        return sheet;
    }
}
